﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

//DB - S
using System.Collections;
//using System.Data;
using System.Data.SqlClient;
//DB - E

namespace NH70_Debugger
{
    public partial class Search_DB : Form
    {
        public Search_DB()
        {
            InitializeComponent();
            InitCtrl();
        }

        private void InitCtrl()
        {
            textBoxServerIP.Text    = "localhost";
            textBoxDB.Text          = "NH70DB";
            textBoxDBID.Text        = "sa";
            textBoxPWD.Text         = "";
        }

        private void buttonSearch_Click(object sender, EventArgs e)
        {

            listViewDB.Clear();

            if (textBoxServerIP.TextLength < 1)
                return;
            else if (textBoxDB.TextLength < 1)
                return;
            else if (textBoxDBID.TextLength < 1)
                return;
            else if (textBoxPWD.TextLength < 1)
                return;
            else if (textBoxQuery.TextLength < 1)
                return;

            string sConn = "server=" + textBoxServerIP.Text + ";database=" + textBoxDB.Text + ";user id=" + textBoxDBID.Text + "; password=" + textBoxPWD.Text;
            SqlConnection conn = new SqlConnection(sConn);

            try{

                conn.Open();// Open Connection
            }
            catch(Exception ex){

                //throw ex;

                Console.WriteLine("#OPEN ERROR#");
                Console.WriteLine(ex);
                MessageBox.Show(ex.Message);

                return;
            }
            

            ArrayList arrRow = new ArrayList();

            string sQuery = textBoxQuery.Text;            

            //2. SqlDataAdapter 생성
            SqlDataAdapter adapter = new SqlDataAdapter();
            //3. Adapter에 SelectCommand 할당
            adapter.SelectCommand = new SqlCommand(sQuery, conn);
            //4. DataSet 생성
            DataSet ds = new DataSet();
            //5. Adapter를 통해서 DataSet 채우기
            //adapter.Fill(ds, "AssetItemCode");
            adapter.Fill(ds);

            conn.Close();// Close Connection

            //7. DataSet으로 작업하기
            //DataTable table = ds.Tables["AssetItemCode"];
            DataTable table = ds.Tables[0];
            DataRowCollection rows = table.Rows;


            ArrayList arCol = new ArrayList();

            foreach (DataColumn dc in table.Columns)
                arCol.Add(dc.ColumnName);

            if (arCol.Count > 0)
                arrRow.Add(arCol);

            foreach (DataRow dr in rows)
            {
                ArrayList arRow = new ArrayList();

                for (int i = 0; i < table.Columns.Count; i++)
                    arRow.Add(dr[i].ToString());

                if (arRow.Count > 0)
                    arrRow.Add(arRow);
            }

            rows.Clear();
            table.Clear();
            ds.Clear();

            IEnumerator ie = arrRow.GetEnumerator();

            while(ie.MoveNext()){

                arCol = ie.Current as ArrayList;

                foreach (object ob in arCol)
                    listViewDB.Columns.Add(ob.ToString());

                    break;
            }            

            while (ie.MoveNext())
            {
                ListViewItem item = new ListViewItem();                

                arCol = ie.Current as ArrayList;

                foreach (object ob in arCol)
                    item.SubItems.Add(ob.ToString());

                if (item.SubItems.Count > 1)
                {
                    item.SubItems.RemoveAt(0);                    
                    listViewDB.Items.Add(item);
                }
            }

            listViewDB.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize);
            //listViewDB.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent);

            textBoxResult.Text = "총 조회 수 : " + (arrRow.Count-1);

        }
    }
}